package br.com.hmm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.hmm.entidade.Conta;
import br.com.hmm.entidade.Usuario;

public class ContaDAO {

	private Connection conn = null;
	private static final String CREATE_CONTA = "INSERT INTO CONTA(CON_ID, CON_NOME, CON_TIPO, CON_DESCRICAO) VALUES(NULL, ?, ?, ?);";
	private static final String UPDATE_CONTA = "UPDATE CONTA SET CON_NOME = ?, CON_TIPO = ?, CON_DESCRICAO = ? WHERE CON_ID = ? ;";
	private static final String UPDATE_SALDO = "UPDATE CONTA SET CON_SALDO = ? WHERE CON_ID = ? ;";
	private static final String DELETE_CONTA = "DELETE FROM CONTA WHERE CON_ID = ? ;";
	private static final String LIST_CONTA 	 = "SELECT CON_ID, CON_NOME, CON_TIPO, CON_DESCRICAO FROM CONTA ;";
	private static final String FIND_BY_ID 	 = "SELECT CON_ID, CON_NOME, CON_TIPO, CON_DESCRICAO WHERE CON_ID = ? ;";
	//private static final String GET_SALDO 	 = "SELECT DISTINCT (SELECT IFNULL(SUM(TRN_VALOR),0) FROM TRANSACOES WHERE TRN_TIPO = 'entrada' AND CONTA_CON_ID = ? ) as credito, (SELECT IFNULL(SUM(TRN_VALOR),0) FROM TRANSACOES WHERE TRN_TIPO = 'despesa' AND CONTA_CON_ID = ? ) as debito, (SELECT SUM(credito - debito) as saldo) ";  
	private static final String GET_SALDO    = "SELECT DISTINCT (SELECT IFNULL(SUM(TRN_VALOR),0) FROM TRANSACOES WHERE TRN_TIPO = 'entrada' AND CONTA_CON_ID = ? AND DATEDIFF(CURDATE(),TRN_DATA) > 0 ) as credito, (SELECT IFNULL(SUM(TRN_VALOR),0) FROM TRANSACOES WHERE TRN_TIPO = 'despesa' AND CONTA_CON_ID = ? AND DATEDIFF(CURDATE(),TRN_DATA) > 0) as debito, (SELECT SUM(credito - debito) as saldo)";
	public ContaDAO() {

	}

	public boolean inserir(Conta conta) throws Exception {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(CREATE_CONTA);
			stmt.setString(1, conta.getNome());
			stmt.setString(2, conta.getTipo());
			stmt.setString(3, conta.getDescricao());
			if (stmt.executeUpdate() > 0) {
				return true;
			}

		} catch (Exception e) {
			throw new Exception(e.getLocalizedMessage());
		} finally {
			DAOUtil.close(this.conn, stmt);
		}
		return false;
	}

	public boolean atualizar(Conta conta) {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(UPDATE_CONTA);
			stmt.setString(1, conta.getNome());
			stmt.setString(2, conta.getTipo());
			stmt.setString(3, conta.getDescricao());
			stmt.setInt(4, conta.getId());
			if (stmt.executeUpdate() > 0) {
				return true;
			}
		} catch (Exception e) {
			return false;
		} finally {
			DAOUtil.close(this.conn, stmt);
		}

		return false;
	}

	public boolean delete(Conta conta) {
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(DELETE_CONTA);
			stmt.setInt(1, conta.getId());
			if (stmt.executeUpdate() > 0) {
				return true;
			}
		} catch (Exception e) {
			return false;
		} finally {
			DAOUtil.close(this.conn, stmt);
		}

		return false;
	}

	public Conta findById(int id) {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(FIND_BY_ID);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.first() == true) {
				Conta c = new Conta();
				c.setId(rs.getInt(1));
				c.setNome(rs.getString(2));
				c.setTipo(rs.getString(3));
				c.setDescricao(rs.getString(4));
				return c;
			}
		} catch (Exception e) {
			return null;
		} finally {
			DAOUtil.close(this.conn, stmt, rs);
		}

		return null;
	}
	
	
	public List<Conta> list(){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(LIST_CONTA);
			rs = stmt.executeQuery();
			List<Conta> lc = new ArrayList<Conta>();
 			while (rs.next()) {
 				Conta c = new Conta();
 				c.setId(rs.getInt(1));
 				c.setNome(rs.getString(2));
 				c.setTipo(rs.getString(3));
 				c.setDescricao(rs.getString(4));
 				lc.add(c);
 			}
 			if(!lc.isEmpty()){
 				return lc;
 			}
		}catch(Exception e){
			return null;
		}finally{
			DAOUtil.close(this.conn, stmt, rs);			
		}
		
		return null;
	}
	
	public boolean atualizaSaldo(Conta conta) throws SQLException{
		PreparedStatement stmt = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(UPDATE_SALDO);
			stmt.setDouble(1, conta.getSaldo());		
			stmt.setInt(4, conta.getId());
			if (stmt.executeUpdate() > 0) {
				return true;
			}
		} catch (Exception e) {
			throw new SQLException(e.getLocalizedMessage());
		} finally {
			DAOUtil.close(this.conn, stmt);
		}

		return false;
	}

	public double listSaldo(int id) throws SQLException{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			FabricaConexao.getInstancia();
			this.conn = FabricaConexao.conectar();
			stmt = this.conn.prepareStatement(GET_SALDO);
			stmt.setInt(1, id);
			stmt.setInt(2, id);
			rs = stmt.executeQuery();
			double dadosConta = 0.0;
 			if(rs.next()) {
 				dadosConta = rs.getDouble(3); 				
 			}
 			return dadosConta;
		}catch(Exception e){
			throw new SQLException(e.getLocalizedMessage());
		}finally{
			DAOUtil.close(this.conn, stmt, rs);			
		}
		
	}

}